/*
 * Copyright (c) 2015. For Intelligent Group.
 *
 */

package com.intelligent.ispc.export.spc;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.intelligent.ispc.common.utils.StringUtils;
import com.intelligent.ispc.constant.AppConstant;
import com.intelligent.ispc.core.dto.KeyValueDto;
import com.intelligent.ispc.core.dto.SpcConfigDto;
import com.intelligent.ispc.core.dto.TestItemDto;
import com.intelligent.ispc.foundation.poi.*;
import com.intelligent.ispc.r.dto.*;
import com.intelligent.ispc.utils.ExportLabelConstant;
import com.intelligent.ispc.utils.RuleLevelType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;

/**
 * Created by cherry on 2016/4/19.
 */
public class SpcExportWorker implements ExWorker {


    //cell's start coordinate in sheet
    private final Integer[] headIndex = new Integer[]{0, 0};
    private final Integer[] dataIndex = new Integer[]{9, 0};
    //position and size of chart image
    private final int imageStartCol = dataIndex[1] + 3, imageEndCol = dataIndex[1] + 8;
    private SXSSFWorkbook workbook = null;
    private final static int imageColWidth = 5500, cacheRow = 1000;
    private final static int dx2 = 1023, // the x coordinate within the first cell.
            dy2 = 255; // the y coordinate within the first cell.
    private Map<String, CellStyle> cpColorMap = Maps.newHashMap();
    //index of current row to write
    private Integer currentRow = 0;
    //sheets to export
    private List<ExSheet> sheets = new ArrayList<>();
    //default digit number
    private int digNum = 6;
    private int imgSheetIndex = 0, curWrittenItemNum = 0;
    //number of sheets in excel
    private int excelItemCapacity;

    private int count = 0;
    /**
     * package sheets data for single item exporting
     *
     * @param searchParam contain charts image and primaryKey.
     * @param results     contains charts data and original data.
     */
    public void buildSPC4SingleItem(Map<String, Object> searchParam, Map<String, Object> results) {
        Map<String, Object> charts = (Map) searchParam.get("exportCharts");
        ExSheet exSheet = new ExSheet();
        exSheet.setName("Charts");
        String primaryKey = searchParam.get("primaryKey").toString();

        List<ExCell> cellList = Lists.newArrayList();

        List<ExCell> ndcCellList;
        List<ExCell> rcCellList;
        List<ExCell> rcCellListAll = Lists.newArrayList();

        if (charts.containsKey(AppConstant.CHART_TYPE_SPC_ND)) {
            NormalDistributionDto ndcDto = (NormalDistributionDto) results.get(AppConstant.CHART_TYPE_SPC_ND);
            String filePath = (String) charts.get(AppConstant.CHART_TYPE_SPC_ND);
            ndcCellList = buildNDC(ndcDto, filePath);
            cellList.addAll(ndcCellList);
        }
        ndcCellList = null;

        if (charts.containsKey(AppConstant.CHART_TYPE_SPC_SCT)) {
            ScatterDto scatterDto = (ScatterDto) results.get(AppConstant.CHART_TYPE_SPC_SCT);
            String filePath = (String) charts.get(AppConstant.CHART_TYPE_SPC_SCT);
            rcCellList = buildSCT(scatterDto, filePath);

            cellList.addAll(rcCellList);
        }

        List<ExCell> rcRuleList = null;
        boolean flag = false;

        if (charts.containsKey(AppConstant.CHART_TYPE_SPC_RUN)) {
            if (rcRuleList == null) {
                rcRuleList = buildRuleDescription(results);
            }
            List<RuleResultDto> ruleResultList = ((ControlDataDto) results.get(AppConstant.CHART_TYPE_SPC_RUN)).getRuleResultList();
            String filePath = (String) charts.get(AppConstant.CHART_TYPE_SPC_RUN);
            rcCellList = buildRC(ruleResultList, filePath, "Run Chart");
            flag = true;
            rcCellListAll.addAll(rcCellList);
        }

        if (charts.containsKey(AppConstant.CHART_TYPE_SPC_XBAR)) {
            if (rcRuleList == null) {
                rcRuleList = buildRuleDescription(results);
            }
            List<RuleResultDto> ruleResultList = ((ControlDataDto) results.get(AppConstant.CHART_TYPE_SPC_XBAR)).getRuleResultList();
            String filePath = (String) charts.get(AppConstant.CHART_TYPE_SPC_XBAR);
            rcCellList = buildRC(ruleResultList, filePath, "X-Bar Chart");
            flag = true;
            rcCellListAll.addAll(rcCellList);
        }

        if (charts.containsKey(AppConstant.CHART_TYPE_SPC_SD)) {
            if (rcRuleList == null) {
                rcRuleList = buildRuleDescription(results);
            }
            List<RuleResultDto> ruleResultList = ((ControlDataDto) results.get(AppConstant.CHART_TYPE_SPC_SD)).getRuleResultList();
            String filePath = (String) charts.get(AppConstant.CHART_TYPE_SPC_SD);
            rcCellList = buildRC(ruleResultList, filePath, "SD Chart");
            flag = true;
            rcCellListAll.addAll(rcCellList);
        }

        if (charts.containsKey(AppConstant.CHART_TYPE_SPC_R)) {
            if (rcRuleList == null) {
                rcRuleList = buildRuleDescription(results);
            }
            List<RuleResultDto> ruleResultList = ((ControlDataDto) results.get(AppConstant.CHART_TYPE_SPC_R)).getRuleResultList();
            String filePath = (String) charts.get(AppConstant.CHART_TYPE_SPC_R);
            rcCellList = buildRC(ruleResultList, filePath, "R Chart");
            flag = true;
            rcCellListAll.addAll(rcCellList);
        }

        if (charts.containsKey(AppConstant.CHART_TYPE_SPC_MR)) {
            if (rcRuleList == null) {
                rcRuleList = buildRuleDescription(results);
            }
            List<RuleResultDto> ruleResultList = ((ControlDataDto) results.get(AppConstant.CHART_TYPE_SPC_MR)).getRuleResultList();
            String filePath = (String) charts.get(AppConstant.CHART_TYPE_SPC_MR);
            rcCellList = buildRC(ruleResultList, filePath, "MR Chart");
            flag = true;
            rcCellListAll.addAll(rcCellList);
        }
        rcCellList = null;

        if (flag) {
            cellList.addAll(rcRuleList);
            cellList.addAll(rcCellListAll);
        }
        exSheet.setExCells(cellList);
        sheets.add(exSheet);

        ExSheet exItemSheet = new ExSheet();
        exItemSheet.setName((String) searchParam.get("itemName"));
        double[] mrValue = (double[]) results.get("mrValue");
        String[] xxbarValue = (String[]) results.get("xxbarValue");
        List<ExCell> itemsCellList = buildData((List<TestItemDto>) results.get("testItemDtos"), mrValue, xxbarValue, primaryKey);
        exItemSheet.setExCells(itemsCellList);
        sheets.add(exItemSheet);
    }

    /**
     * package sheets data for SPC items exporting
     * @param searchParam contain charts image and primaryKey.
     * @param results contains charts data.
     * @param testItems contains test items data and original data.
     */
    public void buildSPCMultiItem(Map<String, Object> searchParam, Map<String, Object> results, Map<String, Object> testItems) {
        Map<String, Map<String, String>> charts = (Map) searchParam.get("exportCharts");
        List<ExportBaseObject> baseObjectList = (List) searchParam.get("charts");
        ExSheet exChartSheet = new ExSheet();
        String primaryKey = searchParam.get("primaryKey").toString();

        exChartSheet.setName("Summary");
        exChartSheet.setIndex(1);

        List<ExCell> summaryCellList = Lists.newArrayList();
        List<ExCell> ndcCellList = Lists.newArrayList();
        if ((curWrittenItemNum) % excelItemCapacity == 0) {
            currentRow = 0;
        }
        int itemCounter = 1;
        Set keys = charts.keySet();
        Iterator iterator = keys.iterator();
            while (iterator.hasNext()) {
                String key = iterator.next().toString();
                Map<String, Object> allCharts = (Map) results.get(key);
                NormalDistributionDto ndcDataDto = (NormalDistributionDto) allCharts.get(AppConstant.CHART_TYPE_SPC_ND);
                ndcCellList.addAll(buildMultiNDC(ndcDataDto, key, itemCounter));
                itemCounter++;
            }

        summaryCellList.addAll(ndcCellList);
        exChartSheet.setExCells(summaryCellList);
        sheets.add(exChartSheet);
        summaryCellList = null;
        ndcCellList = null;

        int i = curWrittenItemNum;
        int chartsSize = charts.size();
        iterator = keys.iterator();
        for (int k = 0; k < chartsSize; k++) {
            ExSheet exSheet = new ExSheet();
            List<ExCell> cellList = Lists.newArrayList();
            count = 0;
            String name = iterator.next().toString();
            String sheetName = name;
            Map<String, Object> allCharts = (Map) results.get(name);
            char[] chars = sheetName.toCharArray();
            int tempLength = chars.length;
            //replace special character with '_'.
            for (int j = 0; j < tempLength; j++) {
                if (chars[j] == ':' || chars[j] == '\\' || chars[j] == '/' || chars[j] == '?' || chars[j] == '*' || chars[j] == '[' || chars[j] == ']') {
                    chars[j] = '_';
                }
            }
            sheetName = new String(chars);
            exSheet.setName(sheetName);

            List<ExCell> rcCellList;
            List<ExCell> rcCellListAll = Lists.newArrayList();
            boolean flag = false;
            currentRow = 0;
            if (charts.get(name).containsKey(AppConstant.CHART_TYPE_SPC_ND)) {
                NormalDistributionDto ndcDto = (NormalDistributionDto) allCharts.get(AppConstant.CHART_TYPE_SPC_ND);
                String filePath = charts.get(name).get(AppConstant.CHART_TYPE_SPC_ND);
                ndcCellList = buildNDChart(ndcDto, filePath);
                cellList.addAll(ndcCellList);
                count++;
            }
            ndcCellList = null;

            if (charts.get(name).containsKey(AppConstant.CHART_TYPE_SPC_SCT)) {
                currentRow = 0;
                ScatterDto scatterDto = (ScatterDto) allCharts.get(AppConstant.CHART_TYPE_SPC_SCT);
                String filePath = charts.get(name).get(AppConstant.CHART_TYPE_SPC_SCT);
                rcCellList = buildSCTChart(scatterDto, filePath);
                cellList.addAll(rcCellList);
            }

            List<ExCell> rcRuleList = null;
            count = 0;
            if (charts.get(name).containsKey(AppConstant.CHART_TYPE_SPC_RUN)) {
                if (rcRuleList == null) {
                    rcRuleList = buildRuleDescription(results);
                }
                List<RuleResultDto> ruleResultList = ((ControlDataDto) allCharts.get(AppConstant.CHART_TYPE_SPC_RUN)).getRuleResultList();
                String filePath = charts.get(name).get(AppConstant.CHART_TYPE_SPC_RUN);
                rcCellList = buildRChart(ruleResultList, filePath, "Run Chart");
                flag = true;
                rcCellListAll.addAll(rcCellList);
                count++;
            }

            if (charts.get(name).containsKey(AppConstant.CHART_TYPE_SPC_XBAR)) {
                if (rcRuleList == null) {
                    rcRuleList = buildRuleDescription(results);
                }
                List<RuleResultDto> ruleResultList = ((ControlDataDto) allCharts.get(AppConstant.CHART_TYPE_SPC_XBAR)).getRuleResultList();
                String filePath = charts.get(name).get(AppConstant.CHART_TYPE_SPC_XBAR);
                rcCellList = buildRChart(ruleResultList, filePath, "X-Bar Chart");
                flag = true;
                rcCellListAll.addAll(rcCellList);
                count++;
            }

            if (charts.get(name).containsKey(AppConstant.CHART_TYPE_SPC_SD)) {
                if (rcRuleList == null) {
                    rcRuleList = buildRuleDescription(results);
                }
                List<RuleResultDto> ruleResultList = ((ControlDataDto) allCharts.get(AppConstant.CHART_TYPE_SPC_SD)).getRuleResultList();
                String filePath = charts.get(name).get(AppConstant.CHART_TYPE_SPC_SD);
                rcCellList = buildRChart(ruleResultList, filePath, "SD Chart");
                flag = true;
                rcCellListAll.addAll(rcCellList);
                count++;
            }

            if (charts.get(name).containsKey(AppConstant.CHART_TYPE_SPC_R)) {
                if (rcRuleList == null) {
                    rcRuleList = buildRuleDescription(results);
                }
                List<RuleResultDto> ruleResultList = ((ControlDataDto) allCharts.get(AppConstant.CHART_TYPE_SPC_R)).getRuleResultList();
                String filePath = charts.get(name).get(AppConstant.CHART_TYPE_SPC_R);
                rcCellList = buildRChart(ruleResultList, filePath, "R Chart");
                flag = true;
                rcCellListAll.addAll(rcCellList);
                count++;
            }

            if (charts.get(name).containsKey(AppConstant.CHART_TYPE_SPC_MR)) {
                if (rcRuleList == null) {
                    rcRuleList = buildRuleDescription(results);
                }
                List<RuleResultDto> ruleResultList = ((ControlDataDto) allCharts.get(AppConstant.CHART_TYPE_SPC_MR)).getRuleResultList();
                String filePath = charts.get(name).get(AppConstant.CHART_TYPE_SPC_MR);
                rcCellList = buildRChart(ruleResultList, filePath, "MR Chart");
                flag = true;
                rcCellListAll.addAll(rcCellList);
                count++;
            }
            rcCellList = null;

            if (flag) {
                cellList.addAll(rcRuleList);
                cellList.addAll(rcCellListAll);
            }
            rcRuleList = null;
            rcCellListAll = null;
            List<TestItemDto> testItemDtoList = (List<TestItemDto>) testItems.get(name);
            testItems.remove(name);
            double[] mrValue = baseObjectList.get(k).getMrValue();
            String[] xxbarValue = baseObjectList.get(k).getXxbarValue();
            List<ExCell> itemsCellList = buildMultiExportData(testItemDtoList, mrValue, xxbarValue, primaryKey);
            cellList.addAll(itemsCellList);
            itemsCellList = null;
            exSheet.setIndex(i + 1);
            exSheet.setExCells(cellList);
            sheets.add(exSheet);
            i++;
        }
    }

    /**
     * @param searchParam contain charts image, primaryKey and other attributes.
     * @param resultData  contain charts data.
     * @param testItems   contain original data.
     *                    package sheets data
     */
    public void buildSPCMultiItems(Map<String, Object> searchParam, Map<String, Object> resultData, Map<String, Object> testItems) {
        List<KeyValueDto> charts = (List) searchParam.get("exportCharts");
        List<ExportBaseObject> baseObjectList = (List) searchParam.get("charts");
        String primaryKey = searchParam.get("primaryKey").toString();
        searchParam.remove("exportCharts");
        ExSheet exChartSheet = new ExSheet();

        exChartSheet.setName("Summary");
        exChartSheet.setIndex(1);

        List<ExCell> cellList = Lists.newArrayList();
        List<ExCell> ndcCellList = Lists.newArrayList();
        if ((curWrittenItemNum) % excelItemCapacity == 0) {
//            cellList.addAll(buildHead(searchParam));
            currentRow = 0;
        }
        int itemCounter = 1;
        for (KeyValueDto dto : charts) {
            NormalDistributionDto ndcDataDto = (NormalDistributionDto) resultData.get(dto.getKey());
            ndcCellList.addAll(buildMultiNDC(ndcDataDto, dto.getKey(), itemCounter));
            dto = null;
            itemCounter++;
        }

        cellList.addAll(ndcCellList);
        exChartSheet.setExCells(cellList);
        sheets.add(exChartSheet);

        int i = curWrittenItemNum;
        int chartsSize = charts.size();
        for (int k = 0; k < chartsSize; k++) {
            ExSheet exItemSheet = new ExSheet();
            String sheetName = charts.get(k).getKey();
            char[] chars = sheetName.toCharArray();
            int tempLength = chars.length;
            //replace special character with '_'.
            for (int j = 0; j < tempLength; j++) {
                if (chars[j] == ':' || chars[j] == '\\' || chars[j] == '/' || chars[j] == '?' || chars[j] == '*' || chars[j] == '[' || chars[j] == ']') {
                    chars[j] = '_';
                }
            }
            sheetName = new String(chars);
            exItemSheet.setName(sheetName);
            List<TestItemDto> testItemDtoList = (List<TestItemDto>) testItems.get(charts.get(k).getKey());
            double[] mrValue = baseObjectList.get(k).getMrValue();
            String[] xxbarValue = baseObjectList.get(k).getXxbarValue();
//            List<ExCell> itemsCellList = buildMultiExportData(testItemDtoList, mrValue, xxbarValue, primaryKey, charts.get(k).getValue().toString());
            List<ExCell> itemsCellList = buildMultiExportData(testItemDtoList, mrValue, xxbarValue, primaryKey);
            exItemSheet.setExCells(itemsCellList);
            exItemSheet.setIndex(i + 1);

            sheets.add(exItemSheet);
            i++;
        }


    }

    private List<ExCell> buildHead(Map<String, Object> searchParam) {
        String[] searchConditions = ExportLabelConstant.EXPORT_SEARCH_LABELS;
        List<ExCell> exCellList = new ArrayList<>();
        for (int i = 0; i < searchConditions.length; i++) {
            exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + i, headIndex[1]}, searchConditions[i], ExCellType.TEXT, fillTitleStyle()));
        }
        int n = 0;
        exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + n++, headIndex[1] + 1}, searchParam.get("fileNames") == null ? "-" : (String) searchParam.get("fileNames"), ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + n++, headIndex[1] + 1}, searchParam.get("searchKeysString") == null ? "-" : (String) searchParam.get("searchKeysString"), ExCellType.TEXT));

        currentRow = searchConditions.length + 1;

        return exCellList;
    }

    private List<ExCell> buildRuleDescription(Map<String, Object> results) {

        int ruleCounter = 0;
        final int ruleSize = 8;
        List<SpcConfigDto> ccRDtoList = (List<SpcConfigDto>) results.get("ruleList");
        List<ExCell> exCellList = new ArrayList<>();
        String[] rulesDetail = new String[ruleSize];
        List<String[]> ruleNums = new ArrayList<>();
        ccRDtoList.forEach((x) -> ruleNums.add(x.getValue().split(",")));
        rulesDetail[ruleCounter] = ruleNums.get(ruleCounter)[0]
                + " point falls outside the " + ruleNums.get(0)[2].trim() + "σ limit from the centerline";
        ruleCounter++;
        rulesDetail[ruleCounter] = ruleNums.get(ruleCounter)[0]
                + "(or more) consecutive points fall on the same side of the centerline";
        ruleCounter++;
        rulesDetail[ruleCounter] = ruleNums.get(ruleCounter)[0]
                + " (or more) points in a row are continually increasing or decreasing";
        ruleCounter++;
        rulesDetail[ruleCounter] = ruleNums.get(ruleCounter)[0]
                + " points in a row alternate in direction, increasing then decreasing";
        ruleCounter++;
        rulesDetail[ruleCounter] = ruleNums.get(ruleCounter)[1].trim()
                + " out of " + ruleNums.get(ruleCounter)[0] + " consecutive points fall beyond the " + ruleNums.get(ruleCounter)[2].trim() + "σ limit, on the same side of the centerline";
        ruleCounter++;
        rulesDetail[ruleCounter] = ruleNums.get(ruleCounter)[1].trim()
                + " out of the " + ruleNums.get(ruleCounter)[0] + " consecutive points fall beyond the " + ruleNums.get(ruleCounter)[2].trim() + "σ limit, on the same side of the centerline";
        ruleCounter++;
        rulesDetail[ruleCounter] = ruleNums.get(ruleCounter)[0]
                + " points in a row are all within the " + ruleNums.get(ruleCounter)[2].trim() + "σ limit, on either side of the centerline";
        ruleCounter++;
        rulesDetail[ruleCounter] = ruleNums.get(ruleCounter)[0]
                + " points in a row exist with none within the " + ruleNums.get(ruleCounter)[2].trim() + "σ limit, and the points a in both directions from the centerline";
        int listSize = ccRDtoList.size();
        for (int i = 0; i < listSize; i++) {
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + i, dataIndex[1]}, ccRDtoList.get(i).getName(), ExCellType.TEXT));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + i, dataIndex[1] + 1}, rulesDetail[i], ExCellType.TEXT));
        }

        currentRow += ccRDtoList.size() + 2;
        return exCellList;
    }

    private List<ExCell> buildNDC(NormalDistributionDto dto, String filePath) {
        String[] labels = ExportLabelConstant.EXPORT_NDC_LABELS;
        List<ExCell> exCellList = new ArrayList<>();

        int lablesLength = labels.length;
        for (int i = 0; i < lablesLength; i++) {
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + i, dataIndex[1]}, labels[i], ExCellType.TEXT, fillTitleStyle()));
        }

        NDStandardDto standardDto = dto.getStandardDto();
        NDStatisticsDto ndsDataDto = dto.getStatisticsDto();
        ProcessCapabilityDto pcDto = dto.getPcData();

        int n = 1;
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getDataCount(), 0) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getAvgValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getMaxValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getMinValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getRangeValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getStdValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getLclValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getUclValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getKurtosisValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getSkewnessValue(), digNum) + "", ExCellType.TEXT));

        n = 12;
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, standardDto.getGroupValue() + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, standardDto.getUslValue() == null ? "-" : standardDto.getUslValue().toString(), ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, standardDto.getLslValue() == null ? "-" : standardDto.getLslValue().toString(), ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, standardDto.getCenterValue() == null ? "-" : standardDto.getCenterValue().toString(), ExCellType.TEXT));


        n = 17;
        int tempDigit = (digNum - 2) >= 0 ? (digNum - 2) : 0;
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, (pcDto.getCaData() == null ? "-" : formatDouble(pcDto.getCaData().getValue(), tempDigit) + "%"),
                ExCellType.TEXT, pcDto.getCaData() == null ? null : fillPcColor(pcDto.getCaData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, (pcDto.getCpkData() == null ? "-" : formatDouble(pcDto.getCpkData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCpkData() == null ? null : fillPcColor(pcDto.getCpkData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, (pcDto.getCpData() == null ? "-" : formatDouble(pcDto.getCpData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCpData() == null ? null : fillPcColor(pcDto.getCpData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, (pcDto.getCplData() == null ? "-" : formatDouble(pcDto.getCplData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCplData() == null ? null : fillPcColor(pcDto.getCplData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, (pcDto.getCpuData() == null ? "-" : formatDouble(pcDto.getCpuData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCpuData() == null ? null : fillPcColor(pcDto.getCpuData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{0, 0, dx2, dy2, imageStartCol, currentRow, imageEndCol, currentRow + n + 2}, filePath, ExCellType.IMAGE));
        currentRow = currentRow + labels.length + 4;
        return exCellList;
    }

    private List<ExCell> buildNDChart(NormalDistributionDto dto, String filePath) {
        String[] labels = ExportLabelConstant.EXPORT_SPC_NDC_LABELS;
        List<ExCell> exCellList = new ArrayList<>();
        Map<String, CellStyle> cellStyleMap = CellStyleUtil.getStyle(getCurrentWorkbook());
        CellStyle cellStyle = cellStyleMap.get(CellStyleType.spc_title.toString());
        int lablesLength = labels.length;
        for (int i = 0; i < lablesLength; i++) {
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, dataIndex[1] + 1 + i}, labels[i], ExCellType.TEXT, cellStyle));
        }
        ProcessCapabilityDto pcDto = dto.getPcData();

        int n = 1;
        int tempDigit = (digNum - 2) >= 0 ? (digNum - 2) : 0;
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + 1, dataIndex[1] + n++}, (pcDto.getCaData() == null ? "-" : formatDouble(pcDto.getCaData().getValue(), tempDigit) + "%"),
                ExCellType.TEXT, pcDto.getCaData() == null ? null : fillPcColor(pcDto.getCaData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + 1, dataIndex[1] + n++}, (pcDto.getCpkData() == null ? "-" : formatDouble(pcDto.getCpkData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCpkData() == null ? null : fillPcColor(pcDto.getCpkData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + 1, dataIndex[1] + n++}, (pcDto.getCpData() == null ? "-" : formatDouble(pcDto.getCpData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCpData() == null ? null : fillPcColor(pcDto.getCpData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + 1, dataIndex[1] + n++}, (pcDto.getCplData() == null ? "-" : formatDouble(pcDto.getCplData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCplData() == null ? null : fillPcColor(pcDto.getCplData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + 1, dataIndex[1] + n++}, (pcDto.getCpuData() == null ? "-" : formatDouble(pcDto.getCpuData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCpuData() == null ? null : fillPcColor(pcDto.getCpuData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{0, 0, dx2, dy2, imageStartCol - 2, currentRow + 2, imageEndCol - 2, currentRow + 20}, filePath, ExCellType.IMAGE));
        currentRow = currentRow + 22;
        return exCellList;
    }

    private List<ExCell> buildMultiNDC(NormalDistributionDto dto, String itemName, int itemCounter) {
        List<ExCell> exCellList = new ArrayList<>();
        if (currentRow == 0) {
            String[] fstLabels = ExportLabelConstant.EXPORT_NDC_FIRST_LABELS;
            String[] scdLabels = ExportLabelConstant.EXPORT_NDC_SECOND_LABELS;

            Map<String, CellStyle> cellStyleMap = CellStyleUtil.getStyle(getCurrentWorkbook());
            CellStyle cellStyle = cellStyleMap.get(CellStyleType.spc_title.toString());
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, 0}, "", ExCellType.TEXT, cellStyle));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, 1}, fstLabels[0], ExCellType.TEXT, cellStyle));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, 11}, fstLabels[1], ExCellType.TEXT, cellStyle));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, 15}, fstLabels[2], ExCellType.TEXT, cellStyle));
            currentRow++;

            int tempLength = scdLabels.length;
            for (int i = 0; i < tempLength; i++) {
                exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, i}, scdLabels[i], ExCellType.TEXT, cellStyle));
            }
            currentRow++;
        }
        NDStandardDto standardDto = dto.getStandardDto();
        NDStatisticsDto ndsDataDto = dto.getStatisticsDto();
        ProcessCapabilityDto pcDto = dto.getPcData();

        int n = 0;
        Map<String, CellStyle> cellStyleMap = CellStyleUtil.getStyle(getCurrentWorkbook());
        ExCell hyperCell = ExUtil.fillToCell(new Integer[]{currentRow, n++}, itemName, ExCellType.HYPERLINK.withCode(ExSheet.formatName(itemCounter, itemName)),
                cellStyleMap.get(CellStyleType.summary_content_testItems.toString()));

        CellStyle textCellStyle = cellStyleMap.get(CellStyleType.spc_value.toString());
        exCellList.add(hyperCell);
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, formatDouble(ndsDataDto.getDataCount(), 0) + "", ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, formatDouble(ndsDataDto.getAvgValue(), digNum) + "", ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, formatDouble(ndsDataDto.getMaxValue(), digNum) + "", ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, formatDouble(ndsDataDto.getMinValue(), digNum) + "", ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, formatDouble(ndsDataDto.getRangeValue(), digNum) + "", ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, formatDouble(ndsDataDto.getStdValue(), digNum) + "", ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, formatDouble(ndsDataDto.getLclValue(), digNum) + "", ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, formatDouble(ndsDataDto.getUclValue(), digNum) + "", ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, formatDouble(ndsDataDto.getKurtosisValue(), digNum) + "", ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, formatDouble(ndsDataDto.getSkewnessValue(), digNum) + "", ExCellType.TEXT, textCellStyle));

        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, standardDto.getGroupValue() + "", ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, (standardDto.getUslValue() == null ? "-" : standardDto.getUslValue().toString()), ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, (standardDto.getLslValue() == null ? "-" : standardDto.getLslValue().toString()), ExCellType.TEXT, textCellStyle));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, (standardDto.getCenterValue() == null ? "-" : standardDto.getCenterValue().toString()), ExCellType.TEXT, textCellStyle));


        int tempDigit = (digNum - 2) >= 0 ? (digNum - 2) : 0;
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, (pcDto.getCaData() == null ? "-" : formatDouble(pcDto.getCaData().getValue(), tempDigit) + "%"), ExCellType.TEXT,
                pcDto.getCaData() == null ? textCellStyle : fillPcColor(pcDto.getCaData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, (pcDto.getCpkData() == null ? "-" : formatDouble(pcDto.getCpkData().getValue(), digNum) + ""), ExCellType.TEXT,
                pcDto.getCpkData() == null ? textCellStyle : fillPcColor(pcDto.getCpkData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, (pcDto.getCpData() == null ? "-" : formatDouble(pcDto.getCpData().getValue(), digNum) + ""), ExCellType.TEXT,
                pcDto.getCpData() == null ? textCellStyle : fillPcColor(pcDto.getCpData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, (pcDto.getCplData() == null ? "-" : formatDouble(pcDto.getCplData().getValue(), digNum) + ""), ExCellType.TEXT,
                pcDto.getCplData() == null ? textCellStyle : fillPcColor(pcDto.getCplData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, n++}, (pcDto.getCpuData() == null ? "-" : formatDouble(pcDto.getCpuData().getValue(), digNum) + ""), ExCellType.TEXT,
                pcDto.getCpuData() == null ? textCellStyle : fillPcColor(pcDto.getCpuData().getLevel())));

        currentRow++;
        return exCellList;
    }

    private List<ExCell> buildSCT(ScatterDto dto, String filePath) {
        String[] labels = ExportLabelConstant.EXPORT_SCT_LABELS;
        List<ExCell> exCellList = new ArrayList<>();

        int lablesLength = labels.length;
        for (int i = 0; i < lablesLength; i++) {
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + i, dataIndex[1]}, labels[i], ExCellType.TEXT, fillTitleStyle()));
        }

        NDStandardDto standardDto = dto.getStandardDto();
        NDStatisticsDto ndsDataDto = dto.getStatisticsDto();
        ProcessCapabilityDto pcDto = dto.getPcData();

        int n = 1;
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getDataCount(), 0) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getAvgValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getMaxValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getMinValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getRangeValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getStdValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getLclValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getUclValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getKurtosisValue(), digNum) + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, formatDouble(ndsDataDto.getSkewnessValue(), digNum) + "", ExCellType.TEXT));

        n++;
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, standardDto.getGroupValue() + "", ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, standardDto.getUslValue() == null ? "-" : standardDto.getUslValue().toString(), ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, standardDto.getLslValue() == null ? "-" : standardDto.getLslValue().toString(), ExCellType.TEXT));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, standardDto.getCenterValue() == null ? "-" : standardDto.getCenterValue().toString(), ExCellType.TEXT));


        n++;
        int tempDigit = (digNum - 2) >= 0 ? (digNum - 2) : 0;
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, (pcDto.getCaData() == null ? "-" : formatDouble(pcDto.getCaData().getValue(), tempDigit) + "%"),
                ExCellType.TEXT, pcDto.getCaData() == null ? null : fillPcColor(pcDto.getCaData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, (pcDto.getCpkData() == null ? "-" : formatDouble(pcDto.getCpkData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCpkData() == null ? null : fillPcColor(pcDto.getCpkData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, (pcDto.getCpData() == null ? "-" : formatDouble(pcDto.getCpData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCpData() == null ? null : fillPcColor(pcDto.getCpData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, (pcDto.getCplData() == null ? "-" : formatDouble(pcDto.getCplData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCplData() == null ? null : fillPcColor(pcDto.getCplData().getLevel())));
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + n++, dataIndex[1] + 1}, (pcDto.getCpuData() == null ? "-" : formatDouble(pcDto.getCpuData().getValue(), digNum) + ""),
                ExCellType.TEXT, pcDto.getCpuData() == null ? null : fillPcColor(pcDto.getCpuData().getLevel())));

        exCellList.add(ExUtil.fillToCell(new Integer[]{0, 0, dx2, dy2, imageStartCol, currentRow, imageEndCol, currentRow + n + 2}, filePath, ExCellType.IMAGE));
        currentRow = currentRow + labels.length + 4;
        return exCellList;
    }

    private List<ExCell> buildSCTChart(ScatterDto dto, String filePath) {
        int startCol = 1;
        int endCol = 6;
        if (count == 0) {
            startCol = dataIndex[1] + 1;
            endCol = dataIndex[1] + 6;
        } else if (count == 1) {
            startCol = dataIndex[1] + 7;
            endCol = dataIndex[1] + 12;
        }
        List<ExCell> exCellList = new ArrayList<>();
        if (count == 0) {
            String[] labels = ExportLabelConstant.EXPORT_SPC_NDC_LABELS;
            Map<String, CellStyle> cellStyleMap = CellStyleUtil.getStyle(getCurrentWorkbook());
            CellStyle cellStyle = cellStyleMap.get(CellStyleType.spc_title.toString());
            int lablesLength = labels.length;
            for (int i = 0; i < lablesLength; i++) {
                exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, startCol + i}, labels[i], ExCellType.TEXT, cellStyle));
            }

            ProcessCapabilityDto pcDto = dto.getPcData();

            int n = 0;
            int tempDigit = (digNum - 2) >= 0 ? (digNum - 2) : 0;
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + 1, startCol + n++}, (pcDto.getCaData() == null ? "-" : formatDouble(pcDto.getCaData().getValue(), tempDigit) + "%"),
                    ExCellType.TEXT, pcDto.getCaData() == null ? null : fillPcColor(pcDto.getCaData().getLevel())));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + 1, startCol + n++}, (pcDto.getCpkData() == null ? "-" : formatDouble(pcDto.getCpkData().getValue(), digNum) + ""),
                    ExCellType.TEXT, pcDto.getCpkData() == null ? null : fillPcColor(pcDto.getCpkData().getLevel())));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + 1, startCol + n++}, (pcDto.getCpData() == null ? "-" : formatDouble(pcDto.getCpData().getValue(), digNum) + ""),
                    ExCellType.TEXT, pcDto.getCpData() == null ? null : fillPcColor(pcDto.getCpData().getLevel())));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + 1, startCol + n++}, (pcDto.getCplData() == null ? "-" : formatDouble(pcDto.getCplData().getValue(), digNum) + ""),
                    ExCellType.TEXT, pcDto.getCplData() == null ? null : fillPcColor(pcDto.getCplData().getLevel())));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + 1, startCol + n++}, (pcDto.getCpuData() == null ? "-" : formatDouble(pcDto.getCpuData().getValue(), digNum) + ""),
                    ExCellType.TEXT, pcDto.getCpuData() == null ? null : fillPcColor(pcDto.getCpuData().getLevel())));
        }
        exCellList.add(ExUtil.fillToCell(new Integer[]{0, 0, dx2, dy2, startCol, currentRow + 2, endCol, currentRow + 20}, filePath, ExCellType.IMAGE));
        currentRow = currentRow + 22;
        return exCellList;
    }

    private List<ExCell> buildRC(List<RuleResultDto> ruleResultList, String filePath, String rcharName) {
        List<ExCell> exCellList = new ArrayList<>();
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, dataIndex[1]}, rcharName, ExCellType.TEXT, fillTitleStyle()));
        currentRow += 1;
        int i = 0;
        for (RuleResultDto ruleResultDto : ruleResultList) {
            String ruleName = ruleResultDto.getRuleName();
            boolean isOverRule = ruleResultDto.isOverRule();
            if (ruleName == "R1") {
                if (isOverRule) {
                    ruleName = "[" + ruleName + "-" + ruleResultDto.getPos() + "]";
                } else {
                    ruleName = "[" + ruleName + "]";
                }
                exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, dataIndex[1]}, ruleName, ExCellType.TEXT, fillRcColor(isOverRule)));
            } else {
                ruleName = "[" + ruleName + "]";
                exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + i, dataIndex[1]}, ruleName, ExCellType.TEXT, fillRcColor(isOverRule)));
            }
            i++;
        }

        int length = ExportLabelConstant.EXPORT_NDC_LABELS.length;
        exCellList.add(ExUtil.fillToCell(new Integer[]{0, 0, dx2, dy2, imageStartCol, currentRow - 1, imageEndCol, currentRow + length + 2}, filePath, ExCellType.IMAGE));

        currentRow += length + 4;

        return exCellList;
    }

    private List<ExCell> buildRChart(List<RuleResultDto> ruleResultList, String filePath, String rcharName) {
        int startCol = 1;
        int endCol = 6;
        if (count == 0) {
            startCol = dataIndex[1] + 1;
            endCol = dataIndex[1] + 6;
        } else if (count == 1) {
            startCol = dataIndex[1] + 7;
            endCol = dataIndex[1] + 12;
        } else if (count == 2) {
            startCol = dataIndex[1] + 1;
            currentRow += 20;
            endCol = dataIndex[1] + 6;
        } else if (count == 3) {
            startCol = dataIndex[1] + 7;
            endCol = dataIndex[1] + 12;
        } else if (count == 4) {
            startCol = dataIndex[1] + 13;
            endCol = dataIndex[1] + 18;
        }
        List<ExCell> exCellList = new ArrayList<>();
        exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow - 1, startCol - 1}, rcharName, ExCellType.TEXT, fillTitleStyle()));
        int i = 0;
        for (RuleResultDto ruleResultDto : ruleResultList) {
            String ruleName = ruleResultDto.getRuleName();
            boolean isOverRule = ruleResultDto.isOverRule();
            if (ruleName == "R1") {
                if (isOverRule) {
                    ruleName = "[" + ruleName + "-" + ruleResultDto.getPos() + "]";
                } else {
                    ruleName = "[" + ruleName + "]";
                }
                exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow, startCol - 1}, ruleName, ExCellType.TEXT, fillRcColor(isOverRule)));
            } else {
                ruleName = "[" + ruleName + "]";
                exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + i, startCol - 1}, ruleName, ExCellType.TEXT, fillRcColor(isOverRule)));
            }
            i++;
        }
        exCellList.add(ExUtil.fillToCell(new Integer[]{0, 0, dx2, dy2, startCol, currentRow, endCol, currentRow + 18}, filePath, ExCellType.IMAGE));

        return exCellList;
    }

    private List<ExCell> buildData(List<TestItemDto> dtos, double[] mrValue, String[] xxbarValue, String primaryKey) {

        String[] itemLabels = ExportLabelConstant.EXPORT_ITEM_LABELS;
        List<ExCell> exCellList = new ArrayList<>();

        exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0], headIndex[1]}, primaryKey, ExCellType.TEXT, fillTitleStyle()));
        int itemLabelsLength = itemLabels.length;
        for (int i = 1; i < itemLabelsLength; i++) {
            exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0], headIndex[1] + i}, itemLabels[i], ExCellType.TEXT, fillTitleStyle()));
        }

        int j = 0;
        int dtoCounter = 0;
        for (TestItemDto testItemDto : dtos) {
            exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + dtoCounter + 1, headIndex[1]}, testItemDto.getPrimaryValue(), ExCellType.TEXT));
            exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + dtoCounter + 1, headIndex[1] + 1}, testItemDto.getUsl(), ExCellType.TEXT));
            exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + dtoCounter + 1, headIndex[1] + 2}, testItemDto.getLsl(), ExCellType.TEXT));
            exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + dtoCounter + 1, headIndex[1] + 3}, testItemDto.getUnitName(), ExCellType.TEXT));
            exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + dtoCounter + 1, headIndex[1] + 4}, testItemDto.getValue(), ExCellType.TEXT));
            String passValue = "";
            if (dtos.get(dtoCounter).getPass() != null) {
                passValue = dtos.get(dtoCounter).getPass() ? "PASS" : "FAIL";
            }
            exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + dtoCounter + 1, headIndex[1] + 5}, passValue, ExCellType.TEXT));
            exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + dtoCounter + 1, headIndex[1] + 6}, xxbarValue[dtoCounter], ExCellType.TEXT));
            if (dtos.get(dtoCounter) != null && dtos.get(dtoCounter).getNumeric() && !isBlank(dtos.get(dtoCounter).getValue())) {
                exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + dtoCounter + 1, headIndex[1] + 7}, formatDouble(mrValue[j], digNum) + "", ExCellType.TEXT));
                j++;
            } else {
                exCellList.add(ExUtil.fillToCell(new Integer[]{headIndex[0] + dtoCounter + 1, headIndex[1] + 7}, "-", ExCellType.TEXT));
            }
            dtos.set(dtoCounter, null);
            dtoCounter++;
        }
        return exCellList;
    }

    private List<ExCell> buildMultiExportData(List<TestItemDto> dtos, double[] mrValue, String[] xxbarValue, String primaryKey) {

        if (count == 0) {
            currentRow -= 20;
        }
        currentRow += 2;
        String[] itemLabels = ExportLabelConstant.EXPORT_ITEM_LABELS;
        List<ExCell> exCellList = new ArrayList<>();
        final int picHeight = 16, picStartCol = 1, picWith = 5, picStartRow = 1;
        Map<String, CellStyle> cellStyleMap = CellStyleUtil.getStyle(getCurrentWorkbook());
        CellStyle cellStyle = cellStyleMap.get(CellStyleType.spc_title.toString());
//        exCellList.add(ExUtil.fillToCell(new Integer[]{0, 0, dx2, dy2, picStartRow, picStartCol, picStartRow + picWith, picStartCol + picHeight}, imagePath, ExCellType.IMAGE));
        exCellList.add(ExUtil.fillToCell(new Integer[]{picStartCol + picHeight + 1 + headIndex[0] + currentRow, picStartCol}, primaryKey, ExCellType.TEXT, cellStyle));
        int itemLabelsLength = itemLabels.length;
        for (int i = 1; i < itemLabelsLength; i++) {
            exCellList.add(ExUtil.fillToCell(new Integer[]{picStartCol + picHeight + 1 + headIndex[0] + currentRow, picStartCol + i}, itemLabels[i], ExCellType.TEXT, cellStyle));
        }
        int j = 0;
        CellStyle textCellStyle = cellStyleMap.get(CellStyleType.spc_value.toString());
        int dtoCounter = 0;
        for (TestItemDto testItemDto : dtos) {
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + picStartCol + picHeight + 1 + headIndex[0] + dtoCounter + 1, picStartCol}, testItemDto.getPrimaryValue(), ExCellType.TEXT, textCellStyle));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + picStartCol + picHeight + 1 + headIndex[0] + dtoCounter + 1, picStartCol + 1}, testItemDto.getUsl(), ExCellType.TEXT, textCellStyle));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + picStartCol + picHeight + 1 + headIndex[0] + dtoCounter + 1, picStartCol + 2}, testItemDto.getLsl(), ExCellType.TEXT, textCellStyle));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + picStartCol + picHeight + 1 + headIndex[0] + dtoCounter + 1, picStartCol + 3}, testItemDto.getUnitName(), ExCellType.TEXT, textCellStyle));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + picStartCol + picHeight + 1 + headIndex[0] + dtoCounter + 1, picStartCol + 4}, testItemDto.getValue(), ExCellType.TEXT, textCellStyle));
            String passValue = "";
            if (dtos.get(dtoCounter).getPass() != null) {
                passValue = dtos.get(dtoCounter).getPass() ? "PASS" : "FAIL";
            }
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + picStartCol + picHeight + 1 + headIndex[0] + dtoCounter + 1, picStartCol + 5}, passValue, ExCellType.TEXT, textCellStyle));
            exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + picStartCol + picHeight + 1 + headIndex[0] + dtoCounter + 1, picStartCol + 6}, xxbarValue[dtoCounter], ExCellType.TEXT, textCellStyle));
            if (dtos.get(dtoCounter) != null && dtos.get(dtoCounter).getNumeric() && !isBlank(dtos.get(dtoCounter).getValue())) {
                exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + picStartCol + picHeight + 1 + headIndex[0] + dtoCounter + 1, picStartCol + 7}, formatDouble(mrValue[j], digNum) + "", ExCellType.TEXT, textCellStyle));
                j++;
            } else {
                exCellList.add(ExUtil.fillToCell(new Integer[]{currentRow + picStartCol + picHeight + 1 + headIndex[0] + dtoCounter + 1, picStartCol + 7}, "-", ExCellType.TEXT, textCellStyle));
            }
            dtos.set(dtoCounter, null);
            dtoCounter++;
        }

        return exCellList;
    }

    private CellStyle fillPcColor(String level) {
        CellStyle cellStyle = getCpColorMap().get(level);
        addBorder(cellStyle);
        return getCpColorMap().get(level);
    }

    private CellStyle fillRcColor(boolean isOverRule) {
        CellStyle style = this.getCurrentWorkbook().createCellStyle();
        Font font = this.getCurrentWorkbook().createFont();
        font.setColor(IndexedColors.BLACK.index);
        if (isOverRule) {
            font.setColor(IndexedColors.RED.index);
        }
        style.setAlignment(HorizontalAlignment.RIGHT);
        style.setFont(font);
        return style;
    }

    private CellStyle fillTitleStyle() {
        CellStyle style = this.getCurrentWorkbook().createCellStyle();
        Font font = this.getCurrentWorkbook().createFont();
        font.setBold(true);
        style.setFillForegroundColor(IndexedColors.BLUE.index);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);
        return style;
    }

    private String formatDouble(double str, int dig) {
        return String.format("%." + dig + "f", str);
    }

    private Map<String, CellStyle> getCpColorMap() {
        if (cpColorMap.size() == 0) {
            XSSFCellStyle style = (XSSFCellStyle) this.getCurrentWorkbook().createCellStyle();
            style.setFillForegroundColor(IndexedColors.GREEN.index);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            addBorder(style);
            XSSFFont font = (XSSFFont) workbook.createFont();
            style.setAlignment(HorizontalAlignment.CENTER);
            font.setColor(IndexedColors.BLACK.index);
            style.setFont(font);
            cpColorMap.put(RuleLevelType.EXCELLENT.toString(), style);

            style = (XSSFCellStyle) this.getCurrentWorkbook().createCellStyle();
            style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.index);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            addBorder(style);
            font = (XSSFFont) workbook.createFont();
            style.setAlignment(HorizontalAlignment.CENTER);
            font.setColor(IndexedColors.BLACK.index);
            style.setFont(font);
            cpColorMap.put(RuleLevelType.GOOD.toString(), style);

            style = (XSSFCellStyle) this.getCurrentWorkbook().createCellStyle();
            style.setFillForegroundColor(IndexedColors.YELLOW.index);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            addBorder(style);
            font = (XSSFFont) workbook.createFont();
            style.setAlignment(HorizontalAlignment.CENTER);
            font.setColor(IndexedColors.BLACK.index);
            style.setFont(font);
            cpColorMap.put(RuleLevelType.ACCEPTABLE.toString(), style);

            style = (XSSFCellStyle) this.getCurrentWorkbook().createCellStyle();
            style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.index);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            addBorder(style);
            font = (XSSFFont) workbook.createFont();
            style.setAlignment(HorizontalAlignment.CENTER);
            font.setColor(IndexedColors.BLACK.index);
            style.setFont(font);
            cpColorMap.put(RuleLevelType.ABOVE_RECTIFICATION.toString(), style);

            style = (XSSFCellStyle) this.getCurrentWorkbook().createCellStyle();
            style.setFillForegroundColor(IndexedColors.RED.index);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            addBorder(style);
            font = (XSSFFont) workbook.createFont();
            style.setAlignment(HorizontalAlignment.CENTER);
            font.setColor(IndexedColors.BLACK.index);
            style.setFont(font);
            cpColorMap.put(RuleLevelType.RECTIFICATION.toString(), style);
        }

        return cpColorMap;
    }

    @Override
    public SXSSFWorkbook getCurrentWorkbook() {
        return workbook;
    }

    @Override
    public List<ExSheet> getSheets() {
        return sheets;
    }

    private boolean isBlank(String d) {
        if (StringUtils.isBlank(d)) {
            return true;
        }

        if (d.equalsIgnoreCase("N/A") || d.equalsIgnoreCase("-") || d.equalsIgnoreCase("nil") || d.equalsIgnoreCase("UnKown Line") || d.equalsIgnoreCase("_")) {
            return true;
        }

        return false;
    }

    public int[] getChartImageInfo() {
        return new int[]{imgSheetIndex, imageStartCol, imageEndCol, imageColWidth, curWrittenItemNum};
    }

    public int getCurWrittenItemNum() {
        return curWrittenItemNum;
    }

    public void setCurWrittenItemNum(int curWrittenItem) {
        this.curWrittenItemNum = curWrittenItem;
    }

    public int getExcelItemCapacity() {
        return excelItemCapacity;
    }

    public void setExcelItemCapacity(int excelItemCapacity) {
        this.excelItemCapacity = excelItemCapacity;
    }

    /**
     * initial worker
     *
     * @param path excel path.
     */
    public void initWorkbook(String path) {
        try {
            FileInputStream fileInputStream = new FileInputStream(path);
            workbook = new SXSSFWorkbook(new XSSFWorkbook(fileInputStream), cacheRow);
            fileInputStream.close();
        } catch (FileNotFoundException e) {
            workbook = new SXSSFWorkbook(cacheRow);
        } catch (IOException e) {
            e.printStackTrace();
        }
        cpColorMap.clear();
    }

    public void setDigNum(int digNum) {
        this.digNum = digNum;
    }

    /**
     * clear worker up
     */
    public void cleanExportWorker() {
        sheets.clear();
        cpColorMap.clear();
        workbook = null;
    }

    private void addBorder(CellStyle cellStyle) {
        BorderStyle borderStyle = BorderStyle.THIN;
        cellStyle.setBorderBottom(borderStyle);
        cellStyle.setBorderLeft(borderStyle);
        cellStyle.setBorderRight(borderStyle);
        cellStyle.setBorderTop(borderStyle);
    }
}


